Append Macro enabled excel file

Hello ,

I have a xlsm file in which it needs few particular sheets needs to be appended from the DXL script directly . Can anybody help me in this aspect with the functions .

 

DXL (output) -> append *. xlsm (Sheet 1 , Sheet 2  ) 

 


doors@36677 - Wed Jul 04 11:05:34 EDT 2018

Re: Append Macro enabled excel file
Mike.Scharnow - Thu Jul 05 16:28:34 EDT 2018

A forum search on "create excel sheet" gives these posts and more. These posts look promising.

https://www.ibm.com/developerworks/community/forums/html/topic?id=39b8523e-6417-4787-98b8-c905fedc8685

https://www.ibm.com/developerworks/community/forums/html/topic?id=16c2da66-e8cf-4daa-b239-e2241958ae56

https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014866917

https://www.ibm.com/developerworks/community/forums/html/topic?id=e7749444-1edd-4592-ba22-f71c9588d0a6

Re: Append Macro enabled excel file
doors@36677 - Sun Jul 15 23:36:17 EDT 2018

hi all,

 

any update on the above ? please suggest ... just need to append the values in the macro enabled excel .. in different sheets ..

Re: Append Macro enabled excel file
Mike.Scharnow - Mon Jul 16 12:13:05 EDT 2018

You are not creating a macro enabled excel file, you are creating a tab seperated text file, also known as TSV file. Excel is so kind to automatically recognize that your file extension (xlsm) has nothing to do with the content, but that does not make it an XLSM file.  

TSV is the same als CSV only the separator is not comma or semicolon but tab.

CSV (or TSV) does not know the concept of sheets, so you cannot write the values for two sheets into one file. You will need a completely different approach

 

By the way, if you open an existing XLSM file with the append mode and just add some lines with tabs, I highly doubt that you will be successful, after all, an XLSM file is a zipped file structure with the content residing in XML files, appending lines will only make the XLSM file corrupt.

 

The options I currently see, are:

Option 1:

  • write a DXL script that creates two different text files
    • content_for_sheet_1.tsv
    • content_for_sheet_2.tsv
  • Create an XLTM file (template with macros enabled) for your sheets. Create a macro in the XLTM that reads the values from the TSV files
  • Create a new XLSM from the XLTM, start your DOORS script to create the TSV files, start the macro to read in the TSV files and write them into Excel cells

Option 2, the same only without macro:

  • write and start your DXL script from above
  • Create an Excel file, use "import external data" to import the first tsv to your first sheet and your second tsv to your second sheet

Option 3:

  • Create a DXL script that uses OLE automation to directly "talk" to Excel. See the links above for a start. In this approach, you can use Excel commands to open a XLSM file, create, name and activate specific sheets and to write values to these sheets.